﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace TodayWhoModel
{
    public class UserServiceStatsModel
    {
        private int userId;

        public int UserId
        {
            get { return userId; }
            set { userId = value; }
        }
        private int serviceId;

        public int ServiceId
        {
            get { return serviceId; }
            set { serviceId = value; }
        }

        private string serviceName;

        public string ServiceName
        {
            get { return serviceName; }
            set { serviceName = value; }
        }
        
        private int clickCount;

        public int ClickCount
        {
            get { return clickCount; }
            set { clickCount = value; }
        }
        private DateTime clickDay;

        public DateTime ClickDay
        {
            get { return clickDay; }
            set { clickDay = value; }
        }


        public bool UpdateServiceCount()
        {   
            // @today i 9 saat ileri aliyorum turkiyeye esitlemek icin. 
            string ssql = @"DECLARE @exist int;
                            DECLARE @serviceId int;
                            DECLARE @today datetime;

                            SET @today = (SELECT CONVERT(VARCHAR(10),DATEADD(hour,9,GETDATE()),111))
                            SET @serviceId = (SELECT serviceId FROM [Services] WHERE serviceName=@serviceName)
                            
                            IF @serviceId IS NOT NULL
                            BEGIN
                                SET @exist = (
			                                SELECT COUNT(*) FROM [User-Services-Stats] 
				                                    WHERE userId=@userId AND serviceId=@serviceId AND
					                                clickDay=@today)
                                IF @exist = 0
	                                INSERT INTO [User-Services-Stats] (userId,serviceId,clickDay) 
		                                VALUES(@userId,@serviceId,@today) 
                                ELSE 
	                                UPDATE [User-Services-Stats] SET clickCount=clickCount+1
		                                WHERE userId=@userId AND clickDay=@today AND serviceId=@serviceId
                            END";
            SqlCommand cmd = new SqlCommand(ssql);

            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@serviceName", ServiceName);
            return DatabaseHelper.ExecuteNonQuerySqlWithoutIdentity(cmd);
        }

        public SqlDataReader RetrieveServicesToday()
        {
            string ssql = "SELECT * FROM [User-Services-Stats] as USS INNER JOIN [Services] as S ON USS.serviceId=S.serviceId ";
            ssql += " WHERE userId=@userId AND clickDay=@clickDay ";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@clickDay", ClickDay);
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

        public SqlDataReader RetrieveServicesLastWeek()
        {
            string ssql = @"SELECT SUM(clickCount) as sum, S.serviceName FROM [User-Services-Stats] as USS 
		                        INNER JOIN [Services] as S ON USS.serviceId=S.serviceId
                                    WHERE userId=@userId AND clickDay<=@today AND clickDay>=@lastweek GROUP BY S.serviceName";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@today", ClickDay);
            cmd.Parameters.AddWithValue("@lastweek", ClickDay.AddDays(-6));
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

        public SqlDataReader RetrieveServicesLastMonth()
        {
            string ssql = @"SELECT SUM(clickCount) as sum, S.serviceName FROM [User-Services-Stats] as USS 
		                        INNER JOIN [Services] as S ON USS.serviceId=S.serviceId
                                    WHERE userId=@userId AND clickDay<=@today AND clickDay>=@lastmonth GROUP BY S.serviceName";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@today", ClickDay);
            cmd.Parameters.AddWithValue("@lastmonth", ClickDay.AddMonths(-1));
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

        public SqlDataReader RetrieveServicesTotal()
        {
            string ssql = @"SELECT SUM(clickCount) as sum, S.serviceName FROM [User-Services-Stats] as USS 
		                        INNER JOIN [Services] as S ON USS.serviceId=S.serviceId
                                    WHERE userId=@userId GROUP BY S.serviceName";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@userId", UserId);
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

        public SqlDataReader RetrieveServiceIds()
        {
            string ssql = @"SELECT USS.serviceId,serviceName FROM [User-Services-Stats] as USS
                                INNER JOIN [Services] as S ON USS.serviceId=S.serviceId  
                                    WHERE userId=@userId GROUP BY USS.serviceId,serviceName ";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@userId", UserId);
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

        public SqlDataReader RetrieveServicesDetailsToday()
        {
            string ssql = @"SELECT * FROM [User-Services-Stats] as USS INNER JOIN [Services] as S ON USS.serviceId=S.serviceId  
                            WHERE userId=@userId AND USS.serviceId=@serviceId AND clickDay=@clickDay ";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@clickDay", ClickDay);
            cmd.Parameters.AddWithValue("@serviceId", ServiceId);
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }
        public SqlDataReader RetrieveServicesDetailsLastWeek()
        {
            string ssql = @"SELECT * FROM [User-Services-Stats] as USS 
		                        INNER JOIN [Services] as S ON USS.serviceId=S.serviceId
                                    WHERE userId=@userId AND USS.serviceId=@serviceId AND clickDay<=@today AND clickDay>=@lastweek ORDER BY clickDay ";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@today", ClickDay);
            cmd.Parameters.AddWithValue("@lastweek", ClickDay.AddDays(-6));
            cmd.Parameters.AddWithValue("@serviceId", ServiceId);
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }
        public SqlDataReader RetrieveServicesDetailsLastMonth()
        {
            string ssql = @"SELECT * FROM [User-Services-Stats] as USS 
		                        INNER JOIN [Services] as S ON USS.serviceId=S.serviceId
                                    WHERE userId=@userId AND USS.serviceId=@serviceId AND clickDay<=@today AND clickDay>=@lastmonth ORDER BY clickDay ";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@today", ClickDay);
            cmd.Parameters.AddWithValue("@lastmonth", ClickDay.AddMonths(-1));
            cmd.Parameters.AddWithValue("@serviceId", ServiceId);
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }
        public SqlDataReader RetrieveServicesDetailTotal()
        {
            string ssql = @"SELECT * FROM [User-Services-Stats] as USS 
		                        INNER JOIN [Services] as S ON USS.serviceId=S.serviceId
                                    WHERE userId=@userId AND USS.serviceId=@serviceId ORDER BY clickDay ";

            SqlCommand cmd = new SqlCommand(ssql);
            cmd.Parameters.AddWithValue("@userId", UserId);
            cmd.Parameters.AddWithValue("@serviceId", ServiceId);
            return DatabaseHelper.ExecuteSelectSql(cmd);
        }

    }
}
